package StudentAchievementManagementSystem.ui;

import StudentAchievementManagementSystem.component.AddStuGradeDialog;
import StudentAchievementManagementSystem.component.DeleteStuGradeDialog;
import StudentAchievementManagementSystem.component.UpdateStuGradeDialog;
import StudentAchievementManagementSystem.mysql.MysqlUtil;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class StuGraComponent extends Box{
    final int WIDTH=850;
    final int HEIGHT=600;

    JFrame jf = null;
    private JTable table;
    private Vector<String> titles;
    private Vector<Vector> tableData;
    private DefaultTableModel tableModel;

    private JTable gradeTable;
    private Vector<String> gradeTitles;
    private Vector<Vector> gradeTableData;
    private DefaultTableModel gradeTableModel;

    public StuGraComponent(JFrame jf) throws SQLException, IOException, ClassNotFoundException {
        //垂直布局
        super(BoxLayout.Y_AXIS);
        //组装视图
        this.jf = jf;
        JPanel btnPanel = new JPanel();
        Color color = new Color(203,220,217);
        btnPanel.setBackground(color);
        btnPanel.setMaximumSize(new Dimension(WIDTH,80));
        btnPanel.setLayout(new FlowLayout(FlowLayout.RIGHT));


        JComboBox deptBox = new JComboBox();
        deptBox.addItem("计算机学院");

        JComboBox majorBox = new JComboBox();
        majorBox.addItem("计算机科学");
        majorBox.addItem("软件工程");
        majorBox.addItem("网络工程");
        majorBox.addItem("通信工程");

        JComboBox classBox = new JComboBox();
        classBox.addItem("001");
        classBox.addItem("002");

        JButton flushBtn = new JButton("查询");

        flushBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                String dept=(String)deptBox.getSelectedItem();
                String class_ = (String) classBox.getSelectedItem();
                String  major = (String) majorBox.getSelectedItem();
                try {
                    requestData(dept, major, class_);
                    requestGradeData(dept, major, class_);
                } catch (IOException ex) {
                    ex.printStackTrace();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                } catch (ClassNotFoundException classNotFoundException) {
                    classNotFoundException.printStackTrace();
                }
            }
        });



        btnPanel.add(deptBox);
        btnPanel.add(majorBox);
        btnPanel.add(classBox);
        btnPanel.add(flushBtn);

        this.add(btnPanel);

        //组装班级成绩表格
        String[] ts = {"学号","课程号","姓名","专业","学科","分数"};
        titles = new Vector<>();
        for (String title : ts) {
            titles.add(title);
        }

        tableData = new Vector<>();

        tableModel = new DefaultTableModel(tableData,titles);
        table = new JTable(tableModel){
            @Override
            public boolean isCellEditable(int row, int column) {
                return false;
            }
        };
        //设置只能选中一行
        table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);

        JScrollPane scrollPane = new JScrollPane(table);
        Box vBox=Box.createVerticalBox();
//        this.add(scrollPane);

//       组装成绩分布表
        String[] gradets = {"0-60","60-70","70-80","80-90","90-100","最高分","最低分","平均分"};
        gradeTitles = new Vector<>();
        for (String title : gradets) {
            gradeTitles.add(title);
        }
        gradeTableData = new Vector<>();

        gradeTableModel = new DefaultTableModel(gradeTableData,gradeTitles);
        gradeTable = new JTable(gradeTableModel){
            @Override
            public boolean isCellEditable(int row, int column) {
                return false;
            }
        };
        //设置只能选中一行
        gradeTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
        JScrollPane gradeScrollPane = new JScrollPane(gradeTable);
//        this.add(gradeScrollPane);
        vBox.add(scrollPane);
        vBox.add(gradeScrollPane);
        this.add(vBox);

    }

    //请求数据
    public void requestData(String dept,String major,String class_) throws SQLException, IOException, ClassNotFoundException {
        Connection connection = MysqlUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql = "select student.sno,course.cno,sname,major,course.cname,grade from student ,studentcoursegrade,course where (student.major='"+major+"' and class_='"+class_+"' and studentcoursegrade.sno=student.sno and course.cno=studentcoursegrade.cno) ";
        Vector<Vector> vectors = new Vector<Vector>();
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            Vector<String> vector1 = new Vector < String > ();
            for (int i=1;i<7;i++){
                vector1.add(resultSet.getString(i));
            }
            try{
                vectors.add(vector1);
//                vector1.clear();
            }catch (NullPointerException e){
                e.printStackTrace();
            }
        }
        //清空tableData的数据
        tableData.clear();
        for (Vector vector : vectors) {
            tableData.add(vector);
        }
        //刷新表格
        tableModel.fireTableDataChanged();
    }
    public void requestGradeData(String dept,String major,String class_) throws SQLException, IOException, ClassNotFoundException {
        Connection connection = MysqlUtil.getConnection();
        Statement statement = connection.createStatement();
        String sql1 = "select  count(student.sno) from studentcoursegrade,student where (student.major='"+major+"'and student.class_='"+class_+"' and  studentcoursegrade.sno=student.sno  and studentcoursegrade.grade between 60 and 70)";
        String sql2 = "select  count(student.sno) from student ,studentcoursegrade where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno  and studentcoursegrade.grade between 60 and 70) ";
        String sql3 = "select  count(student.sno)from student,studentcoursegrade  where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno and studentcoursegrade.grade between 70 and 80) ";
        String sql4 = "select  count(student.sno)from student ,studentcoursegrade where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno  and studentcoursegrade.grade between 80 and 90) ";
        String sql5 = "select  count(student.sno) from student ,studentcoursegrade where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno  and studentcoursegrade.grade between 90 and 100) ";
        String sql6 = "select  max(studentcoursegrade.grade) from student ,studentcoursegrade where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno )  ";
        String sql7 = "select  min(studentcoursegrade.grade) from student ,studentcoursegrade where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno )  ";
        String sql8 = "select  avg(studentcoursegrade.grade) from student ,studentcoursegrade where (student.major='"+major+"' and student.class_='"+class_+"' and studentcoursegrade.sno=student.sno ) ";
        Vector<Vector> vectors = new Vector<Vector>();
        Vector<String> vector =new Vector<>();
        ResultSet resultSet1 = statement.executeQuery(sql1);
        while(resultSet1.next()){
            vector.add(resultSet1.getString(1));
        }
        ResultSet resultSet2 = statement.executeQuery(sql2);
        while(resultSet2.next()){
            vector.add(resultSet2.getString(1));
        }
        ResultSet resultSet3 = statement.executeQuery(sql3);
        while(resultSet3.next()){
            vector.add(resultSet3.getString(1));
        }
        ResultSet resultSet4 = statement.executeQuery(sql4);
        while(resultSet4.next()){
            vector.add(resultSet4.getString(1));
        }
        ResultSet resultSet5 = statement.executeQuery(sql5);
        while(resultSet5.next()){
            vector.add(resultSet5.getString(1));
        }
        ResultSet resultSet6 = statement.executeQuery(sql6);
        while(resultSet6.next()){
            vector.add(resultSet6.getString(1));
        }
        ResultSet resultSet7 = statement.executeQuery(sql7);
        while(resultSet7.next()){
            vector.add(resultSet7.getString(1));
        }
        ResultSet resultSet8 = statement.executeQuery(sql8);
        while(resultSet8.next()){
            vector.add(resultSet8.getString(1));
        }

        gradeTableData.clear();
        gradeTableData.add(vector);
        //刷新表格
        gradeTableModel.fireTableDataChanged();
    }
}

